package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.db.Table;

import db.beans.T_KeywordBean;
public class T_KeywordTable extends Table{
	public static final String COL_KEY_WORD_NO = "KEY_WORD_NO";
	public static final String COL_RANKING = "RANKING";
	public static final String COL_DES_KEY_WORD_1 = "DES_KEY_WORD_1";
	public static final String COL_DES_KEY_WORD_2 = "DES_KEY_WORD_2";
	public static final String COL_DES_KEY_WORD_3 = "DES_KEY_WORD_3";
	public static final String COL_DES_KEY_WORD_4 = "DES_KEY_WORD_4";
	public static final String COL_DES_KEY_WORD_5 = "DES_KEY_WORD_5";
	public static final String COL_STATUS = "STATUS";
	public static final String COL_DELETE_FLAG = "DELETE_FLAG";

	public static T_KeywordBean[] selectAll(Connection conn, String tableName) throws SQLException{
		final String sql =
			"SELECT "
			+ COL_KEY_WORD_NO
			+ "," + COL_RANKING
			+ "," + COL_DES_KEY_WORD_1
			+ "," + COL_DES_KEY_WORD_2
			+ "," + COL_DES_KEY_WORD_3
			+ "," + COL_DES_KEY_WORD_4
			+ "," + COL_DES_KEY_WORD_5
			+ "," + COL_STATUS
			+ "," + COL_DELETE_FLAG
			+ " FROM " + tableName;

		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<T_KeywordBean> v = new Vector<T_KeywordBean>();
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i;
			while (rs.next()) {
				i = 0;
				T_KeywordBean rec = new T_KeywordBean();
				rec.setKeywordNo(rs.getLong(++i));
				rec.setSrcKeyword(rs.getString(++i));
				rec.setRanking(rs.getInt(++i));
				rec.setDesKeyword1(rs.getString(++i));
				rec.setDesKeyword2(rs.getString(++i));
				rec.setDesKeyword3(rs.getString(++i));
				rec.setDesKeyword4(rs.getString(++i));
				rec.setDesKeyword5(rs.getString(++i));
				rec.setStatus(rs.getInt(++i));
				rec.setDeleteFlag(rs.getInt(++i) == DELETE_FLAG);
				v.add(rec);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
		return (T_KeywordBean[]) v.toArray(new T_KeywordBean[v.size()]);
	}
	public static T_KeywordBean[] selectByTopCount(Connection conn, String tableName, int topCount) throws SQLException{
		final String sql =
			"SELECT "
			+ COL_KEY_WORD_NO
			+ "," + COL_RANKING
			+ "," + COL_DES_KEY_WORD_1
			+ "," + COL_DES_KEY_WORD_2
			+ "," + COL_DES_KEY_WORD_3
			+ "," + COL_DES_KEY_WORD_4
			+ "," + COL_DES_KEY_WORD_5
			+ "," + COL_STATUS
			+ "," + COL_DELETE_FLAG
			+ " FROM " + tableName;

		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<T_KeywordBean> v = new Vector<T_KeywordBean>();
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i;
			while (rs.next()) {
				i = 0;
				T_KeywordBean rec = new T_KeywordBean();
				rec.setKeywordNo(rs.getLong(++i));
				rec.setSrcKeyword(rs.getString(++i));
				rec.setRanking(rs.getInt(++i));
				rec.setDesKeyword1(rs.getString(++i));
				rec.setDesKeyword2(rs.getString(++i));
				rec.setDesKeyword3(rs.getString(++i));
				rec.setDesKeyword4(rs.getString(++i));
				rec.setDesKeyword5(rs.getString(++i));
				rec.setStatus(rs.getInt(++i));
				rec.setDeleteFlag(rs.getInt(++i) == DELETE_FLAG);
				v.add(rec);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
		return (T_KeywordBean[]) v.toArray(new T_KeywordBean[v.size()]);
	}

	public static void insert(Connection conn, String strTableName, T_KeywordBean arrKeywordBean) throws SQLException{
		final String sql = "INSERT INTO " + strTableName + "(" + COL_KEY_WORD_NO
						+ "," + COL_RANKING
						+ "," + COL_DES_KEY_WORD_1
						+ "," + COL_DES_KEY_WORD_2
						+ "," + COL_DES_KEY_WORD_3
						+ "," + COL_DES_KEY_WORD_4
						+ "," + COL_DES_KEY_WORD_5
						+ "," + COL_STATUS
						+ ") VALUES (?,?,?,?)";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			int index = 0;
			stmt.setLong(++index, arrKeywordBean.getKeywordNo());
			stmt.setInt(++index, arrKeywordBean.getRanking());
			stmt.setString(++index, arrKeywordBean.getDesKeyword1());
			stmt.setString(++index, arrKeywordBean.getDesKeyword2());
			stmt.setString(++index, arrKeywordBean.getDesKeyword3());
			stmt.setString(++index, arrKeywordBean.getDesKeyword4());
			stmt.setString(++index, arrKeywordBean.getDesKeyword5());
			stmt.setInt(++index, arrKeywordBean.getStatus());
			stmt.setBoolean(++index, arrKeywordBean.getDeleteFlag());
			stmt.executeUpdate();
		} finally{
			if(stmt!=null) {
				stmt.close();
			}
			conn.commit();
		}
	}

	public static String selectDesKeywordBySrcKeyword(Connection conn, String tableName, String srcKeyword) throws SQLException{
		final String sql = "SELECT " + COL_DES_KEY_WORD_1 + " FROM " + tableName
						+ " WHERE "
						+ COL_DES_KEY_WORD_1 + "=?"
						;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String desKeyword = "";
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			if (rs.next()) {
				desKeyword = rs.getString(1);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
		}
		return desKeyword;
	}
}
